package dblayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.microsoft.sqlserver.jdbc.SQLServerException;

import modellayer.Appointment;
import modellayer.Company;
import modellayer.CompanyType;
import modellayer.KeyPerson;
import modellayer.Service;
import modellayer.ZipCode;

/**
 * The CompanyDB class to exchange data betwixt SQL database and java
 * @author Allan Eriksen, Andreas Rye, Casper Madsen, Christian Stidsborg, Stefan Bols
 */

public class CompanyDB
{
	private Connection con;
	private boolean fromKeyPerson = false;
	
	/**
	 * CompanyDB Fields
	 * @param con
	 * @param fromKeyPerson
	 */
	
	
	/**
	 * Constructor for the CompanyDB class, also creates a connection to the SQL Database
	 */
	public CompanyDB()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	
	/**
	 * Search method using cvr
	 * @param cvr
	 * @return singleWhere(wClause)
	 */
	public Company findCompanyByCvr(int cvr)
	{
		String wClause = " cvr='"+cvr+"' ";
		
		return singleWhere(wClause);
	}
	
	/**
	 * singleWhere method to execute a method in the database
	 * @param wClause
	 * @return companyObj
	 */
	private Company singleWhere(String wClause)
	{
		ResultSet results;
		Company companyObj = null;
		
		String query = buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if (results.next())
			{
				companyObj = buildCompany(results);
			}
			else
			{
				companyObj = null;
			}
		}
		catch (Exception e)
		{
			System.out.println("SQL query: " +query);
			System.out.println("Error: " +e);
		}
		
		return companyObj;
	}
	
	/**
	 * Method to create a query which searches the SQL database for a company ArrayList 
	 * @param id
	 * @param fromKeyPerson
	 * @return miscWhere(query)
	 */
	public ArrayList<Company> findCompaniesByKeyPerson(int id, boolean fromKeyPerson)
	{
		this.fromKeyPerson = fromKeyPerson;
		//String query = "SELECT sem2_Company.* FROM sem2_Company, sem2_KeyPerson, sem2_KeyPersonsCompanies WHERE sem2_KeyPerson.keyPersonId='"+id+"' AND sem2_KeyPersonsCompanies.companyId=sem2_Company.cvr AND sem2_KeyPersonsCompanies.keyPersonId=sem2_KeyPerson.keyPersonId";
		String query = "SELECT c.* FROM sem2_Company c, sem2_KeyPerson kp, sem2_KeyPersonsCompanies kpc WHERE kp.keyPersonId = '"+id+"' AND kpc.companyId = c.cvr AND kpc.keyPersonId = kp.keyPersonId";
		
		
		return miscWhere(query);
	}
	
	/**
	 * miscWhere method that returns an ArrayList with company objects
	 * @param query
	 * @return cList
	 */
	private ArrayList<Company> miscWhere(String query)	{
		ArrayList<Company> array = new ArrayList<Company>();
		
		//String query = buildQuery(wClause);
		
		if (query==null) {
			query = "";
			query = buildQuery(query);
		}

		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ResultSet results = stmt.executeQuery(query);
			
			while (results.next())
			{
				array.add(buildCompany(results));
			}
		}
		catch (Exception e)
		{
			System.out.println("SQL query CompanyDB: "+query);
			System.out.println("	error: "+e);
		}
		
		return array;
	}
	
	/**
	 * A method which uses wClause parameters to construct a query, in a format that the SQL database understands
	 * @param wClause
	 * @return query
	 */
	private String buildQuery(String wClause)
	{
		String query = "SELECT cvr, name, type, address, zipcode, mainPhone, mainMail, website, mainServiceId, secServiceId, noOfEmployees, addedDate FROM sem2_company";
		
		if (wClause!=null) {
			if (!wClause.isEmpty()|| !wClause.equals(""))
			{
				query += " WHERE " +wClause;
			}
		}
		
		return query;
	}
	
	/**
	 * A method which extracts data from the database into Java
	 * @param results
	 * @return companyObj
	 */
	private Company buildCompany(ResultSet results)
	{
		Company companyObj = null;
		
		try
		{
			int typeId = results.getInt("type");
			CompanyType companyTypeObj = new CompanyTypeDB().findCompanyTypeById(typeId);
			
			int zip = results.getInt("zipCode");
			ZipCode zipCodeObj = new ZipcodeDB().findZipcode(zip);
			
			int mainServiceId = results.getInt("mainServiceId");
			Service mainServiceObj = new ServiceDB().findServiceById(mainServiceId);
			
			int secServiceId = results.getInt("secServiceId");
			Service secServiceObj = new ServiceDB().findServiceById(secServiceId);
			
			companyObj = new Company(results.getInt("cvr"), results.getString("name"), companyTypeObj, results.getString("address"), zipCodeObj, results.getString("mainPhone"), results.getString("mainMail"), results.getString("website"), mainServiceObj, secServiceObj, results.getInt("noOfEmployees"), results.getLong("addedDate"));
			
			if (!fromKeyPerson)
			{
				int cvr = results.getInt("cvr");
				ArrayList<KeyPerson> kpList = new KeyPersonDB().findKeyPersonsByCompany(cvr, true);
			
				companyObj.setKPList(kpList);
			}
		}
		catch (SQLException e)
		{
			System.out.println("Failed to build Company object. \n Error: " +e);
		}
		
		return companyObj;
	}
	
	/**
	 * Method that finds all the appointments 
	 * @param wClause
	 * @return miscWhere(wClause)
	 */
	public ArrayList<Company> getCompanies(String wClause) {
		return miscWhere(wClause);
	}
	
	/**
	 * Method that is used to delete a company from the SQL database by using the cvr
	 * @param cvr
	 * @return success
	 */
	public boolean deleteCompany(int cvr) throws Exception
	{
		boolean success = false;
		
		String query = "DELETE FROM sem2_Company WHERE cvr='"+cvr+"'";
		
		try 
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeQuery(query);	
			success = true;		
			stmt.close();
		} 
		catch (SQLServerException e) {
			success = true;
		}
		catch (Exception ex) 
		{
			throw new Exception("Company deletetion went wrong, try again please! Error: "+ex);
		}
		
		return success;
			
	}
	
	public int insertCompany(Company c) throws Exception
	{
		int rc = -1;
		
		String query = "INSERT INTO sem2_company (cvr, name, type, address, zipCode, mainPhone, mainMail, website, mainServiceId, secServiceId, noOfEmployees, addedDate) VALUES ('"+c.getCvr()+"', '"+c.getName()+"', '"+c.getCompanyType().getId()+"', '"+c.getAddress()+"', '"+c.getZipCode().getZipCode()+"', '"+c.getMainPhone()+"', '"+c.getMainMail()+"', '"+c.getWebsite()+"', '"+c.getMainServices().getServiceId()+"', '"+c.getSecServices().getServiceId()+"', '"+c.getNoOfEmployees()+"', '"+c.getAddedDate()+"')";
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(SQLException e)
		{
			System.out.println("SQL query: " +query);
			System.out.println("error: " +e);
			
			throw new Exception("Failed to insert Company into Database (CompanyDB)");
		}
		
		return rc;
	}
	
	public ArrayList<Company> listAllCompanies()
	{
		String query = "SELECT sem2_Company.* FROM sem2_Company";
		
		return miscWhere(query);
	}
	
}
